package psl;

import java.sql.*;
import java.util.HashMap;
import java.util.ArrayList;

/**
 * Created by IntelliJ IDEA. User: phiteshkumar Date: Jul 28, 2006 Time: 2:53:13 AM
 */
//--to access this connecton object from any class like DBCon.moConnectin

//--------------------------------------------------------------------------------
//--Setting Values to parameters
//--------------------------------------------------------------------------------
public class DBConn
{
    public static Connection moConnection;
/*
        public static String msDatabaseUserName = "RetailOutlet";
        public static String msDatabasePassword = "password";
        public static String msDatabaseDriver = "oracle.jdbc.driver.OracleDriver";
        String host = "saturn";
        String dbName = "saturn";
        int port = 1521;
        String msDatabaseURL = "jdbc:oracle:thin:@" + host + ":" + port + ":" + dbName;
*/
    //for connection with msAccess
    String msDatabaseURL = "jdbc:odbc:retailoutlet";

    //--to get Connection Very first time when application is started.......

    public DBConn()
    {
        try
        {

            System.out.println("In Database Connection");
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
/*
            Class.forName (msDatabaseDriver);
            moConnection = DriverManager.getConnection(msDatabaseURL, msDatabaseUserName, msDatabasePassword);
*/
            //for connection with msAccess
            moConnection = DriverManager.getConnection(msDatabaseURL);
            // moConnection.setAutoCommit(false);
        }
        catch (ClassNotFoundException e)
        {
            System.out.println("DataBase Driver Not Found");
            System.out.println(e.toString());
        }
        catch (Exception e)
        {
            System.out.println("Exception In getConnection : ");
            System.out.println(e.toString());
        }
    }

    public static void DbClose(DBConn fdb) throws SQLException
    {
        fdb.moConnection.close();
    }

    //--Getting Records
    public static ResultSet getRecordFromDB(String fosQry)
    {
        try
        {
            //Statement loStatement = foConnection.createStatement();
            Statement loStatement = moConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                                 ResultSet.CONCUR_READ_ONLY);
            return loStatement.executeQuery(fosQry);
        }
        catch (Exception ex)
        {
            System.out.println("Problem in fetching");
            return null;
        }
    }

    public static String[][] setRecordsToString(String fsQry, String[] faColName, String fsCondition)
    {
        HashMap laValue = new HashMap();
        String[][] value = null;
        ArrayList lvData = new ArrayList();
        ResultSet loResultSet = null;
        int count = 0;
        if (fsQry.equals(""))
        {
            return value;
        }
        else
        {
            if (fsCondition != null)
            {
                if (!fsCondition.equals(""))
                {
                    fsQry += " where " + fsCondition;
                }
            }
            try
            {
                if (faColName == null || faColName.length == 0)
                {
                    loResultSet = getRecordFromDB(fsQry);
                    if(loResultSet!=null)
                    {
                        while (loResultSet.next())
                        {
                            System.err.println("fsQry::::::"+fsQry);
                            lvData.add(loResultSet.getString(1)+"");
                            count++;
                        }
                        loResultSet.close();
                    }
                }
                else
                {
                    loResultSet = getRecordFromDB(fsQry);
                    while (loResultSet.next())
                    {
                        lvData = new ArrayList();
                        for (int i = 0; i < faColName.length; i++)
                        {
                            lvData.add(loResultSet.getString(faColName[i])+"");
                        }
                        laValue.put("data_" + new Integer(count), lvData);
                        count++;
                    }
                    loResultSet.close();
                }
            }
            catch (Exception ex)
            {
                System.err.println("Problem In setRecordsToString");
                ex.printStackTrace();
            }

            if (faColName == null || faColName.length == 0)
            {
                value = new String[1][lvData.size()];
                for (int j = 0; j < lvData.size(); j++)
                {
                    value[0][j] = (String) lvData.get(j);
                }

                for (int k = 0; k < value.length; k++)
                {
                    for (int l = 0; l < value[0].length; l++)
                    {
                        value[k][l] = value[k][l];
                        System.err.print("  " + value[k][l]);
                    }
                }
            }
            else
            {
                value = new String[count][faColName.length];
                for (int i = 0; i < laValue.size(); i++)
                {
                    ArrayList laData = (ArrayList) laValue.get("data_" + new Integer(i));
                    for (int j = 0; j < laData.size(); j++)
                    {
                        value[i][j] = (String) laData.get(j);
                    }
                }
                String lsValue[][] = new String[faColName.length][count];

                for (int i = 0; i < count; i++)
                {
                    for (int k = 0; k < value[i].length; k++)
                    {
                        for (int l = 0; l < value.length; l++)
                        {
                            lsValue[k][l] = value[l][k];
                        }
                    }
                }
                value = lsValue;
            }
            return value;
        }
    }

    /*
            public static String[] setRecordsToString (String fsQry, String[] faColName, String fsCondition)
            {
                String[] value = null;
                ResultSet loResultSet=null;
                if (fsQry.equals (""))
                {
                    return value;
                }
                else
                {
                    if (fsCondition != null)
                    {
                        if (!fsCondition.equals (""))
                        {
                            fsQry += " where " + fsCondition;
                            System.err.println ("fsQry: "+fsQry);
                        }
                    }
                    try
                    {
                        if (faColName == null || faColName.length == 0)
                        {
                            value = new String[1];
                            loResultSet = getRecordFromDB (fsQry);
                            while (loResultSet.next ())
                            {
                                value[0] = loResultSet.getString (1);
                            }
                            loResultSet.close ();
                        }
                        else
                        {
                            value = new String[faColName.length];
                            loResultSet = getRecordFromDB (fsQry);
                            while (loResultSet.next ())
                            {
                                for (int i = 0; i < faColName.length; i++)
                                {
                                    value[i] = loResultSet.getString (faColName[i]);
                                }
                            }
                            loResultSet.close ();
                        }
                    }
                    catch (Exception ex)
                    {
                        System.err.println ("Problem In setRecordsToString");
                        ex.printStackTrace ();
                    }
                    return value;
                }
            }
    */
    public static String executeUpdate(String fsSQL)
    {
        try
        {
            Statement loStatement = moConnection.createStatement();
            loStatement.execute(fsSQL);
            loStatement.close();
            moConnection.commit();
        }
        catch (SQLException feException)
        {
            //CommonServices.throwError(fsSQL, feException);
            return feException.getMessage();
        }
        return null;
    }
}
